package com.css.gdzj.gdzjbxgd.modules.userinfo.repository.impl;
import com.css.gdzj.gdzjbxgd.modules.userinfo.repository.UserInfoNativeRepository;
import com.css.gdzj.gdzjbxgd.modules.userinfo.repository.entity.UserInfoEntity;
import com.css.gdzj.gdzjbxgd.sys.user.form.LoginForm;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author z
 * @date 2020-12-11
 */
@Repository
public class UserInfoNativeRepositoryImpl implements UserInfoNativeRepository {
    @PersistenceContext
    private EntityManager em;

    private static final Logger log = LoggerFactory.getLogger(UserInfoNativeRepositoryImpl.class);


    @Override
    public Map<String,Object> queryUserInfos(String loginName, String userName, String userPost, String userRank,String userLeaderPost,String userUnit, String userLevel,int curPage, int pageSize) {
        Map<String,Object> map = new HashMap<>();
        StringBuilder sql = new StringBuilder("select * from user_info ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");

        if(!StringUtils.isEmpty(loginName)) {
            whereSql.append(" and LOGIN_NAME like :loginName");
        }
        if(!StringUtils.isEmpty(userName)) {
            whereSql.append(" and USER_NAME like :userName");
        }
        if(!StringUtils.isEmpty(userPost)) {
            whereSql.append(" and USER_POST like :userPost");
        }
        if(!StringUtils.isEmpty(userRank)) {
            whereSql.append(" and USER_RANK like :userRank");
        }
        if(!StringUtils.isEmpty(userLeaderPost)) {
            whereSql.append(" and USER_LEADER_POST = :userLeaderPost");
        }
        if(!StringUtils.isEmpty(userUnit)) {
            whereSql.append(" and USER_UNIT = :userUnit");
        }
        if(!StringUtils.isEmpty(userLevel)) {
          /*  int i = Integer.parseInt(userLevel);*/
            if (userLevel.equals("0")){
                whereSql.append(" and USER_LEVEL = :userLevel");
            }else{
                whereSql.append(" and USER_LEVEL < :userLevel");
            }
        }
        whereSql.append(" order by  LOGIN_NAME ");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), UserInfoEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from user_info ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        if(!StringUtils.isEmpty(loginName)) {
            query.setParameter("loginName", "%" + loginName + "%");
            countQuery.setParameter("loginName", "%" + loginName + "%");
        }
        if(!StringUtils.isEmpty(userName)) {
            query.setParameter("userName", "%" + userName + "%");
            countQuery.setParameter("userName", "%" + userName + "%");
        }
        if(!StringUtils.isEmpty(userPost)) {
            query.setParameter("userPost", "%" + userPost + "%");
            countQuery.setParameter("userPost", "%" + userPost + "%");
        }
        if(!StringUtils.isEmpty(userRank)) {
            query.setParameter("userRank", "%" + userRank + "%");
            countQuery.setParameter("userRank", "%" + userRank + "%");
        }
        if(!StringUtils.isEmpty(userLeaderPost)) {
            query.setParameter("userLeaderPost", userLeaderPost);
            countQuery.setParameter("userLeaderPost", userLeaderPost);
        }
        if(!StringUtils.isEmpty(userUnit)) {
            query.setParameter("userUnit",  userUnit);
            countQuery.setParameter("userUnit", userUnit );
        }
       if(!StringUtils.isEmpty(userLevel)) {
            query.setParameter("userLevel", userLevel );
            countQuery.setParameter("userLevel", userLevel );
        }

        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<UserInfoEntity> list = query.getResultList();
        List<UserInfoEntity> list1 = countQuery.getResultList();
        map.put("listUserInfo",list);
        map.put("total",countQuery.getSingleResult());
        return map;
    }


        @Override
        public List<UserInfoEntity>  queryUserInfo(String userName, String userUnit){
            StringBuilder sql = new StringBuilder("select * from user_info ");
            StringBuilder whereSql = new StringBuilder(" where 1=1 ");
            if(!StringUtils.isEmpty(userName)) {
                whereSql.append(" and USER_NAME like :userName");
            }
            if(!StringUtils.isEmpty(userUnit)) {
                whereSql.append(" and USER_UNIT like :userUnit");
            }
            Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), UserInfoEntity.class);
            StringBuilder countSql = new StringBuilder("select count(*) from user_info ");
            Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
            if(!StringUtils.isEmpty(userName)) {
                query.setParameter("userName", "%" + userName + "%");
                countQuery.setParameter("userName", "%" + userName + "%");
            }
            if(!StringUtils.isEmpty(userUnit)) {
                query.setParameter("userUnit", "%" + userUnit + "%");
                countQuery.setParameter("userUnit", "%" + userUnit + "%");
            }
            return query.getResultList();
        }

    @Override
    public UserInfoEntity getLoginUserInfo(LoginForm loginForm) {
        StringBuilder sql = new StringBuilder("select * from user_info ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!StringUtils.isEmpty(loginForm.getLoginName())) {
            whereSql.append(" and LOGIN_NAME = :loginName");
        }
        if(!StringUtils.isEmpty(loginForm.getPassword())) {
            whereSql.append(" and LOGIN_PASSWORD = :loginPassword");
        }
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), UserInfoEntity.class);
        if(!StringUtils.isEmpty(loginForm.getPassword())) {
            query.setParameter("loginName", "%" + loginForm.getLoginName() + "%");
        }
        if(!StringUtils.isEmpty(loginForm.getPassword())) {
            query.setParameter("loginPassword", "%" + loginForm.getPassword() + "%");
        }
        return (UserInfoEntity) query.getSingleResult();
    }

    @Override
    public Map<String, Object> queryAllUserInfos(String loginName, String userName, String userPoliticalOutlook, String userRank, String userLeaderPost, String userUnit) {
        Map<String,Object> map = new HashMap<>();
        StringBuilder sql = new StringBuilder("select * from user_info ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 and user_level = '0'");
        if(!StringUtils.isEmpty(loginName)) {
            whereSql.append(" and LOGIN_NAME like :loginName");
        }
        if(!StringUtils.isEmpty(userName)) {
            whereSql.append(" and USER_NAME like :userName");
        }
        if(!StringUtils.isEmpty(userPoliticalOutlook)) {
            whereSql.append(" and USER_POLITICAL_OUTLOOK = :userPoliticalOutlook");
        }
        if(!StringUtils.isEmpty(userRank)) {
            whereSql.append(" and USER_RANK = :userRank");
        }
        if(!StringUtils.isEmpty(userLeaderPost)) {
            whereSql.append(" and USER_LEADER_POST = :userLeaderPost");
        }
        if(!StringUtils.isEmpty(userUnit)) {
            whereSql.append(" and USER_UNIT = :userUnit");
        }
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), UserInfoEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from user_info ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        if(!StringUtils.isEmpty(loginName)) {
            query.setParameter("loginName", "%" + loginName + "%");
            countQuery.setParameter("loginName", "%" + loginName + "%");
        }
        if(!StringUtils.isEmpty(userName)) {
            query.setParameter("userName", "%" + userName + "%");
            countQuery.setParameter("userName", "%" + userName + "%");
        }
        if(!StringUtils.isEmpty(userPoliticalOutlook)) {
            query.setParameter("userPoliticalOutlook", userPoliticalOutlook);
            countQuery.setParameter("userPoliticalOutlook", userPoliticalOutlook);
        }
        if(!StringUtils.isEmpty(userRank)) {
            query.setParameter("userRank", userRank);
            countQuery.setParameter("userRank", userRank);
        }
        if(!StringUtils.isEmpty(userLeaderPost)) {
            query.setParameter("userLeaderPost", userLeaderPost);
            countQuery.setParameter("userLeaderPost", userLeaderPost);
        }
        if(!StringUtils.isEmpty(userUnit)) {
            query.setParameter("userUnit", userUnit);
            countQuery.setParameter("userUnit", userUnit);
        }

        List<UserInfoEntity> list = query.getResultList();
        map.put("listUserInfo",list);
//        map.put("total",countQuery.getSingleResult());
        return map;
    }

    @Override
    public Map<String, Object> queryUserStatistic(String userUnit, Integer pageNo, Integer pageSize) {
        Map<String,Object> map = new HashMap<>();
        StringBuilder sql = new StringBuilder("select * from user_info  ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 and user_level = '0' and user_report_flag = '1' ");

        if(!StringUtils.isEmpty(userUnit)) {
            whereSql.append(" and USER_UNIT = :userUnit");
        }

        whereSql.append(" order by  LOGIN_NAME ");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), UserInfoEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from user_info ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());

        if(!StringUtils.isEmpty(userUnit)) {
            query.setParameter("userUnit",  userUnit);
            countQuery.setParameter("userUnit", userUnit );
        }

        Pageable pageable = PageRequest.of(pageNo - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<UserInfoEntity> list = query.getResultList();
        map.put("listUserInfo",list);
        map.put("total",countQuery.getSingleResult());
        return map;
    }

    @Override
    public Map<String, Object> queryUserStatisticCount(String userUnit) {
        Map<String,Object> map = new HashMap<>();
        StringBuilder sql = new StringBuilder("select * from user_info ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 and user_level = '0' and user_report_flag = '1' ");

        if(!StringUtils.isEmpty(userUnit)) {
            whereSql.append(" and USER_UNIT = :userUnit");
        }
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), UserInfoEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from user_info ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());

        if(!StringUtils.isEmpty(userUnit)) {
            query.setParameter("userUnit", userUnit);
            countQuery.setParameter("userUnit", userUnit);
        }

        List<UserInfoEntity> list = query.getResultList();
        map.put("listUserInfo",list);
        return map;
    }
}
